package com.hqd.ch03.bean.jdbc;


import com.hqd.ch03.v24.utils.DataSourceUtils;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Data
@NoArgsConstructor
public class UserServiceImpl {

    private DataSource dataSource;

    public void saveData(User user) throws SQLException {
        PreparedStatement preparedStatement = null;
        try {
            Connection connection = DataSourceUtils.getConnection(dataSource);
            preparedStatement = connection.prepareStatement("insert into user(id,name,age,sex) values (?,?,?,?)");
            preparedStatement.setInt(1, user.getId());
            preparedStatement.setString(2, user.getName());
            preparedStatement.setInt(3, user.getAge());
            preparedStatement.setString(4, user.getSex());
            preparedStatement.executeUpdate();
            int a = 1 / 0;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public List<User> getUsers() throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Connection connection = DataSourceUtils.getConnection(dataSource);
            preparedStatement = connection.prepareStatement("select * from user");
            resultSet = preparedStatement.executeQuery();
            List<User> list = new ArrayList<>();
            while (resultSet.next()) {
                User user = new User();
                BeanUtils.setProperty(user, "id", resultSet.getInt("id"));
                BeanUtils.setProperty(user, "name", resultSet.getString("name"));
                BeanUtils.setProperty(user, "age", resultSet.getInt("age"));
                BeanUtils.setProperty(user, "sex", resultSet.getString("sex"));
                list.add(user);
            }
            return list;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }
    }

    public void updateData(User user) throws SQLException {
        String sql = "UPDATE user SET name = ?,age=?,sex=? WHERE id = ?";
        PreparedStatement preparedStatement = null;
        try {
            Connection connection = DataSourceUtils.getConnection(dataSource);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setInt(2, user.getAge());
            preparedStatement.setString(3, user.getSex());
            preparedStatement.setInt(4, user.getId());
            preparedStatement.executeUpdate();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public void deleteData(int id) throws SQLException {
        String sql = "DELETE FROM user WHERE id =? ";
        PreparedStatement preparedStatement = null;
        try {
            Connection connection = DataSourceUtils.getConnection(dataSource);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }
}
